PostgreSQL 11 で information_schema から FK 制約の一覧を取得する
code:sql
select
tc.constraint_name
, tc.table_name as referring_table
, kcu.column_name as referring_column
, ccu.table_name as referred_table
, ccu.column_name as referred_column
from
information_schema.table_constraints as tc
inner join
information_schema.key_column_usage as kcu
on
tc.constraint_name = kcu.constraint_name
inner join
information_schema.constraint_column_usage as ccu
on
tc.constraint_name = ccu.constraint_name
where
tc.constraint_type = 'FOREIGN KEY'
;
メモ
ほしいのは以下の 4 つの情報。
referring table: 制約がかかっているテーブルの名前。言い換えれば子テーブル。他のカラムの値を参照しているテーブル名。
referring colum: 制約がかかっているカラム名。言い換えれば子カラム。他のテーブルの値を参照している子カラム名。
referred table: 子カラムの値を制約するテーブルの名前。言い換えれば親テーブル。他のカラムから参照されるテーブル名。
referred column: 子カラムの値を制約するカラムの名前。言い換えれば親テーブル。他のカラムから参照されるカラム名。
基本は information_schema.table_constraints。このテーブルの constraint_name カラムには制約名が、table_name カラムには referring table にあたるテーブル名が入ってる。また、where 句で tc.constraint_type = 'FOREIGN KEY' のように constraint_type カラムに格納された制約の種類を指定することで FK 制約のみに絞っている。
referring colum を得るためには information_schema.key_column_usage テーブルを利用する。このテーブルには制約名である constraint_name カラムと referring_column にあたるカラム名が column_name カラムに入っている。そこで、constraint_name を条件に inner join を使ってテーブルを結合し、kcu.column_name のようにして子カラム名を取り出している。
残った 2 つ、referred table と referred column は information_schema.constraint_column_usage から取得できる。このテーブルには制約名である constraint_name カラム、 referred table にあたる table_name カラムおよび referring column にあたる column_name カラムが含まれている。そこで、referring column の場合と同様、 inner join で constraint_name を条件にテーブルを結合し、ccu.table_name as referred_table のようにして親テーブル名と親カラム名を取り出している。
参考